/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package tuitionmanagement.DAO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.Vector;
import tuitionmanagement.entity.RateEntity;
import tuitionmanagement.util.DBUtil;

/**
 *
 * @author XUAN
 */
public class RateDao {

    private Connection con;
    private ResultSet rs;
    private PreparedStatement pst;
    private String lastError;
    private final String sql_selectRate = "select * from Rate";
    private final String sql_selectDate = "select * from Rate where RateDate <= ?";
    private final String sql_insertRate = "insert into Rate(USDRate,VNDRate,RateDate) values(?,?,?)";
    private final String sql_updateRate = "update Rate set RateDate =?, USDRate =?, VNDRate=? where RateId =?";
    private final String sql_deletetRate = "delete from Rate where RateId = ?";

    public Vector<String> GetRate(String date) {
        CallableStatement cs = null;
        ResultSet rs = null;

        Vector<String> rate = new Vector<String>();
        try {
            con = DBUtil.getConn();
            cs = con.prepareCall(sql_selectRate);
            cs.setString(1, date);
            rs = cs.executeQuery();
            if (rs == null) {
                System.out.println("Can not read Student Information!");
            } else {
                while (rs.next()) {
                    rate.addElement(rs.getString(0));
                    rate.addElement(rs.getString(1));
                    rate.addElement(rs.getString(2));
                    rate.addElement(rs.getString(3));
                }
            }
            cs.close();
            DBUtil.closeConn(con);
        } catch (Exception ex) {
            lastError = ex.getMessage();
            //System.out.println(ex.getMessage());
            }
        return rate;
    }

    public ArrayList<RateEntity> readByAll() {
        ArrayList<RateEntity> list = new ArrayList<RateEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectRate);
            rs = pst.executeQuery();
            while (rs.next()) {
                RateEntity r = new RateEntity();
                r.setDateRate(rs.getDate("RateDate"));
                r.setUsd(rs.getInt("USD"));
                r.setVnd(rs.getFloat("VND"));
                list.add(r);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public ArrayList<RateEntity> getDateRateSearch(Date date) {
        ArrayList<RateEntity> list = new ArrayList<RateEntity>();
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_selectDate);
            pst.setDate(1, new java.sql.Date(date.getTime()));
            rs = pst.executeQuery();
            while (rs.next()) {
                RateEntity rate = new RateEntity();
                rate.setRateId(rs.getInt("RateId"));
                rate.setDateRate(rs.getDate("RateDate"));
                rate.setUsd(rs.getInt("USDRate"));
                rate.setVnd(rs.getFloat("VNDRate"));
                list.add(rate);
            }
        } catch (SQLException ex) {
            setLastError("SQL Error!");
        } finally {
            DBUtil.closeConn(con);
        }
        return list;
    }

    public boolean addRate(RateEntity r) {
        if (!r.validate()) {
            setLastError(r.getLastError());
            return false;
        }
        try {
            con = DBUtil.getConn();
            pst = con.prepareStatement(sql_insertRate);            
            pst.setFloat(1, r.getUsd());
            pst.setFloat(2, r.getVnd());

            pst.setDate(3, new java.sql.Date( r.getDateRate().getTime()));

            if (pst.executeUpdate() > 0) {
                setLastError("Add Rate's information successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public boolean update(RateEntity rate) {
        con = DBUtil.getConn();

        try {
            pst = con.prepareStatement(sql_updateRate, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setDate(1, new java.sql.Date(rate.getDateRate().getTime()));
            pst.setFloat(2, rate.getUsd());
            pst.setFloat(3, rate.getVnd());

            pst.setInt(4, rate.getRateId());
            pst.executeUpdate();
            //rs.first();
            //rs.updateDate(1,(Date) rate.getDateRate());
            //rs.updateFloat(2,rate.getUsd());
            //rs.updateFloat(3,rate.getVnd());
            //rs.updateRow();
            con.close();
            setLastError("Update FeeLevel successfully!");
            return true;
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    public boolean delete(int RateId) {
        con = DBUtil.getConn();
        try {
            pst = con.prepareStatement(sql_deletetRate, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            pst.setInt(1, RateId);
            if (pst.executeUpdate() > 0) {
                setLastError("Delete FeeLevel successfully!");
                return true;
            }
        } catch (SQLException ex) {
            setLastError(ex.getMessage());
        } finally {
            DBUtil.closeConn(con);
        }
        return false;
    }

    /**
     * @return the lastError
     */
    public String getLastError() {
        return lastError;
    }

    /**
     * @param lastError the lastError to set
     */
    public void setLastError(String lastError) {
        this.lastError = lastError;
    }
}
